﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Text;
using SAC.Helper;

/// <summary>
/// Code By 单云龙
/// </summary>
public class Cls_Db
{
    public SqlConnection Conn;
    public Cls_Db()
    {
        //
        // TODO: 在此处添加构造函数逻辑
        //
    }
    private void OpenSqlConnection()
    {
        StringBuilder constr = new StringBuilder();

        constr.Append("server=" + IniHelper.ReadIniData("RelationDBbase", "DBIP", null) + ";");
        constr.Append("uid=" + IniHelper.ReadIniData("RelationDBbase", "DBUser", null) + ";");
        constr.Append("pwd=" + IniHelper.ReadIniData("RelationDBbase", "DBPwd", null) + ";");
        constr.Append("database=" + IniHelper.ReadIniData("RelationDBbase", "DBName", null));

        Conn = new SqlConnection(constr.ToString());
        Conn.Open();
    }

    private void CloseSqlConnection()
    {
        Conn.Close();
        Conn.Dispose();
    }

    //返回DATATABLE
    public DataTable GetDataTable(string Sqlstr)
    {
        DataSet ds;
        DataTable dt;
        if (!string.IsNullOrEmpty(Sqlstr))
        {
            OpenSqlConnection();
            try
            {
                SqlDataAdapter sda = new SqlDataAdapter(Sqlstr, Conn);
                ds = new DataSet();
                sda.Fill(ds);
                dt = ds.Tables[0];
            }
            catch
            {
                dt = null;
            }
            CloseSqlConnection();
        }
        else
        {
            dt = null;
        }
        return dt;
    }

    //返回DATASET
    public DataSet GetDataSet(string Sqlstr)
    {
        DataSet ds;
        if (!string.IsNullOrEmpty(Sqlstr))
        {

            OpenSqlConnection();
            try
            {
                SqlDataAdapter sda = new SqlDataAdapter(Sqlstr, Conn);
                ds = new DataSet();
                sda.Fill(ds);
            }
            catch
            {
                ds = null;
            }
            CloseSqlConnection();
        }
        else
        {
            ds = null;
        }
        return ds;
    }

    //执行SQL语句
    public Boolean DoCommand(string Sqlstr)
    {
        Boolean bln = true;
        if (!string.IsNullOrEmpty(Sqlstr))
        {
            OpenSqlConnection();
            try
            {
                SqlCommand cmd = new SqlCommand(Sqlstr, Conn);
                cmd.ExecuteNonQuery();
            }
            catch
            {
                bln = false;
            }
            CloseSqlConnection();
        }
        else
        {
            bln = false;
        }
        return bln;
    }

    //返回数据
    public string GetValue(string Sqlstr)
    {
        string returnStr = "";
        if (!string.IsNullOrEmpty(Sqlstr))
        {
            OpenSqlConnection();
            try
            {
                SqlCommand cmd = new SqlCommand(Sqlstr, Conn);
                returnStr = cmd.ExecuteScalar().ToString();

            }
            catch
            {
                returnStr = "";
            }
            CloseSqlConnection();
        }
        else
        {
            returnStr = "";
        }
        return returnStr;
    }

    private SqlCommand CreateCommand(string procName)
    {
        OpenSqlConnection();
        SqlCommand cmd = new SqlCommand(procName, Conn);
        cmd.CommandType = CommandType.StoredProcedure;
        return cmd;
    }

    public SqlDataReader getPageDisp(string tableName, string getFields, string orderField, int pageSize,
            int pageIndex, int orderType, string strWhere, out int reCordcount, out int pageCount)
    {
        SqlCommand objcmd = CreateCommand("SP_Pagination");
        SqlParameter sp = objcmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int);
        sp.Direction = ParameterDirection.ReturnValue;
        sp = objcmd.Parameters.Add("@tableName", SqlDbType.NVarChar, 100);
        sp.Value = tableName;
        sp = objcmd.Parameters.Add("@GetFields", SqlDbType.NVarChar, 1000);
        sp.Value = getFields;
        sp = objcmd.Parameters.Add("@orderField", SqlDbType.NVarChar, 1000);
        sp.Value = orderField;
        sp = objcmd.Parameters.Add("@PageSize", SqlDbType.Int, 4);
        sp.Value = pageSize;
        sp = objcmd.Parameters.Add("@PageIndex", SqlDbType.Int, 4);
        sp.Value = pageIndex;
        sp = objcmd.Parameters.Add("@OrderType", SqlDbType.Int, 4);
        sp.Value = orderType;
        sp = objcmd.Parameters.Add("@strWhere", SqlDbType.VarChar, 1000);
        sp.Value = strWhere;

        sp = objcmd.Parameters.Add("@ReCordCount", SqlDbType.Int, 4);
        sp.Direction = ParameterDirection.Output;
        sp = objcmd.Parameters.Add("@PageCount", SqlDbType.Int, 4);
        sp.Direction = ParameterDirection.Output;
        objcmd.ExecuteNonQuery();
        reCordcount = Convert.ToInt32(objcmd.Parameters["@ReCordCount"].Value.ToString());
        pageCount = Convert.ToInt32(objcmd.Parameters["@PageCount"].Value.ToString());
        SqlDataReader objsdr = objcmd.ExecuteReader();
        objcmd.Dispose();
        objcmd = null;
        return objsdr;
    }

    public SqlDataReader returnDrBySql(string sql)
    {
        SqlDataReader dr = null;

        OpenSqlConnection();

        try
        {
            SqlCommand cmd = new SqlCommand(sql, Conn);
            dr = cmd.ExecuteReader();
            return dr;

        }
        catch (Exception ce)
        {
            dr.Close();
            return null;
        }
        finally
        {
            CloseSqlConnection();
        }

    }

    /// <summary>
    /// 根据测点名添加测点描述字段（拼写Table）
    /// </summary>
    /// <returns></returns>
    public DataTable returnDTBySql(string sql)
    {
        SqlDataReader dr = null;
        DataTable dt = new DataTable();

        dt.Columns.Add("序号", typeof(string));
        dt.Columns.Add("测点名称", typeof(string));
        dt.Columns.Add("测点描述", typeof(string));
        dt.Columns.Add("观测时间", typeof(string));
        dt.Columns.Add("测值", typeof(string));

        double i = 1;

        if (!string.IsNullOrEmpty(sql))
        {
            OpenSqlConnection();
            try
            {
                SqlCommand cmd = new SqlCommand(sql, Conn);

                dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    DataRow dw = dt.NewRow();

                    dw[0] = i;
                    dw[1] = dr["ID"].ToString();
                    dw[2] = this.GetValue("select ZmName from CSRepDef where ID='" + dr["ID"].ToString() + "'");
                    dw[3] = dr["DT"].ToString();
                    dw[4] = dr["DayValue"].ToString();

                    i++;
                    dt.Rows.Add(dw);
                }
                cmd.Dispose();
            }
            catch { }
            finally
            {
                CloseSqlConnection();
            }

        }
        return dt;
    }


    /// <summary>
    /// 故障信息页面 定制DataTable
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public DataTable returnDTByGZXX(string sql)
    {

        SqlDataReader dr = null;
        DataTable dt = new DataTable();

        dt.Columns.Add("序号", typeof(string));
        dt.Columns.Add("故障名称", typeof(string));
        dt.Columns.Add("启动时间", typeof(string));
        dt.Columns.Add("停止时间", typeof(string));
        dt.Columns.Add("运行时间", typeof(string));

        double i = 1;
        double time;
        if (!string.IsNullOrEmpty(sql))
        {
            OpenSqlConnection();
            try
            {
                SqlCommand cmd = new SqlCommand(sql, Conn);

                dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    DataRow dw = dt.NewRow();

                    dw[0] = i;
                    dw[1] = dr["设备名称"].ToString();
                    dw[2] = dr["启动时间"].ToString();
                    dw[3] = dr["停止时间"].ToString();

                    if (dr["运行时间"].ToString() != "")
                    {
                        int yxTime = int.Parse(dr["运行时间"].ToString());

                        time = yxTime / 3600.00;
                        dw[4] = time.ToString("0.00");
                    }
                    else
                    {
                        dw[4] = dr["运行时间"].ToString();
                    }
                    i++;
                    dt.Rows.Add(dw);
                }
                cmd.Dispose();
            }
            catch { }
            finally
            {
                CloseSqlConnection();
            }

        }
        return dt;
    }

}
